package org.indi.yudidi;


import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;

// 事务2: 可以重复读
public class User2TX {

    private static final Logger LOG = LoggerFactory.getLogger(User2TX.class);

    public static void main(String[] args) throws SQLException {

        String sql = "SELECT username,amount FROM T_USER";
        String plusAmountSQL = "UPDATE T_USER SET amount = ? WHERE username = ?";

        Connection dbConnection = getDBConnection();
        LOG.debug("Begin session2");
        // 开启事务
        dbConnection.setAutoCommit(false);

        PreparedStatement queryPS = dbConnection.prepareStatement(sql);
        ResultSet rs = queryPS.executeQuery();
        Long superManAmount = 0L;
        while (rs.next()) {
            String name = rs.getString(1);
            Long amount = rs.getLong(2);
            LOG.info("{} has amount:{}", name, amount);
//            if (name.equals("SuperMan")) {
//                superManAmount = amount;
//            }
        }

        // 在事务1提交之后, 再次读取, 仍然都是100
        PreparedStatement queryPS2 = dbConnection.prepareStatement(sql);
        ResultSet rs2 = queryPS2.executeQuery();
        Long superManAmount2 = 0L;
        while (rs2.next()) {
            String name = rs2.getString(1);
            Long amount = rs2.getLong(2);
            LOG.info("{} has amount:{}", name, amount);
//            if (name.equals("SuperMan")) {
//                superManAmount2 = amount;
//            }
        }


        dbConnection.commit();

        PreparedStatement queryPS3 = dbConnection.prepareStatement(sql);
        ResultSet rs3 = queryPS3.executeQuery();
        Long superManAmount3 = 0L;
        while (rs3.next()) {
            String name = rs3.getString(1);
            Long amount = rs3.getLong(2);
            LOG.info("{} has amount:{}", name, amount);
//            if (name.equals("SuperMan")) {
//                superManAmount2 = amount;
//            }
        }


        PreparedStatement updatePS = dbConnection.prepareStatement(plusAmountSQL);
        updatePS.setString(1, superManAmount + 100 +"");
        updatePS.setString(2, "zs");
        updatePS.executeUpdate();

        LOG.debug("Done session2!");
        queryPS.close();
        updatePS.close();
        dbConnection.close();
    }

    private void query(String sql){

    }

    private static Connection getDBConnection() throws SQLException {
        String DB_DRIVER = "com.mysql.jdbc.Driver";
        String DB_CONNECTION = "jdbc:mysql://localhost:3306/test";
        String DB_USER = "root";
        String DB_PASSWORD = "admin";
        try {
            Class.forName(DB_DRIVER);
        } catch (ClassNotFoundException e) {
            LOG.error(e.getMessage());
        }
        return DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);
    }
}
